package com.zy.cat.entity.dev.repository;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.zy.cat.entity.dev.CatDev5088InviteIncomeHistory;
 
public interface CatDev5088InviteIncomeHistoryRepository extends JpaSpecificationExecutor<CatDev5088InviteIncomeHistory>,  JpaRepository<CatDev5088InviteIncomeHistory, String>{
	List<CatDev5088InviteIncomeHistory> findByYidAndIstatus(String yid,int istatus);
	
	
	CatDev5088InviteIncomeHistory findByPidAndYid(String pid,String yid);
	
	@Transactional
	@Query(value = "SELECT SUM(a) FROM(SELECT IFNULL(SUM(income),0)a FROM zy_cat_5088_invite_income_data WHERE yid=:pid AND  istatus=2 UNION ALL SELECT IFNULL(SUM(-dmoney),0)a FROM zy_cat_remit_list WHERE pid=:pid AND rtype=5)ls",nativeQuery = true)
	long getYIncome(@Param("pid")String pid);
	
//	/**
//	 * 获取邀请成功的最大索引
//	 * @info 
//	 * @author Link
//	 * @date 2020-06-18 15:52
//	 * @param ypid
//	 * @return
//	 */
//	@Transactional
//	@Query(value = "SELECT IFNULL(MAX(iindex),0) FROM zy_cat_5088_invite_income_data  WHERE yid=:pid AND istatus=2;",nativeQuery = true)
//	int getMaxSuccessIndex(String ypid);
	
	/**
	 * 获取邀请的最大索引
	 * @info 
	 * @author Link
	 * @date 2020-06-18 15:52
	 * @param ypid
	 * @return
	 */
	@Transactional
	@Query(value = "SELECT IFNULL(MAX(iindex),0) FROM zy_cat_5088_invite_income_data  WHERE yid=:ypid",nativeQuery = true)
	int getMaxIndex(@Param("ypid")String ypid);
	
}
